﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Drawing;
using System.Globalization;

namespace sistemaAcademicoFinanceiroEscolas
{
    public partial class exportacaoAula : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            string connectionString = "";
            if (FileUpload1.HasFile)
            {
                string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
                string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                string fileLocation = Server.MapPath("~/up/" + fileName);
                bool extensaoPermitida = false;
                bool arquivoGravado = false;

                if (FileUpload1.HasFile)
                {
                    string extensaoArquivo = Path.GetExtension(FileUpload1.FileName);

                    string[] extensoesPermitidas = { ".xls" };



                    for (int i = 0; i < extensoesPermitidas.Length; i++)
                        if (extensoesPermitidas[i] == extensaoArquivo)
                            extensaoPermitida = true;

                    if (extensaoPermitida == true)
                    {
                        try
                        {
                            FileUpload1.PostedFile.SaveAs(fileLocation);
                            arquivoGravado = true;
                        }
                        catch (Exception ex)
                        {
                            ErroLabel.ForeColor = Color.Red;
                            ErroLabel.Text = "Ocorreu um problema na tentativa de salvar o arquivo." + ex.ToString();
                            ErroLabel.Visible = true;
                            arquivoGravado = false;
                        }
                    }
                    else
                    {
                        ErroLabel.ForeColor = Color.Red;
                        ErroLabel.Text = string.Format("Tipo de arquivo inválido. Somente sao aceitos arquivos .xls.", extensoesPermitidas.ToString());
                        ErroLabel.Visible = true;
                    }

                    if (arquivoGravado)
                    {

                        ErroLabel.ForeColor = Color.Blue;
                        ErroLabel.Text = string.Format("Arquivo gravado com sucesso.");
                        ErroLabel.Visible = true;
                    }
                }
                FileUpload1.SaveAs(fileLocation);

                //Check whether file extension is xls or xslx

                if (fileExtension == ".xls")
                {
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                }
                else if (fileExtension == ".xlsx")
                {
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                }

                //Create OleDB Connection and OleDb Command

                OleDbConnection con = new OleDbConnection(connectionString);
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Connection = con;
                OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
                DataTable dtExcelRecords = new DataTable();
                con.Open();

                DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
                cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
                dAdapter.SelectCommand = cmd;
                dAdapter.Fill(dtExcelRecords);
                con.Close();

                //criação de strings para receber as linhas do excel



                SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqlc.Open();
                SqlConnection sqld = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqld.Open();

                string nomeProfessor = "";
                string codigoProfessor = "";
                string nomeAluno = "";
                string codigoAluno = "";
                string codigoDisciplina = "";
                string descricaoDisciplina = "";
                string dataAula = "";
                string horaInicialAula = "";
                string horaFinalAula = "";

                foreach (DataRow row in dtExcelRecords.Rows) // Loop over the rows.
                {
                    try
                    {

                        nomeProfessor = row[5].ToString();
                        nomeAluno = row[6].ToString();

                        if (nomeProfessor == "" || nomeAluno == "")
                        {
                            break;

                        }                       


                        dataAula = Convert.ToDateTime(row[2].ToString()).ToString("MM/dd/yyyy");
                        dataAula = dataAula.Substring(3, 2) + "/" + dataAula.Substring(0, 2) + "/" + dataAula.Substring(6, 4);

                        //string d = Convert.ToDateTime(row[2].ToString()).ToString("dd/MM/yyyy");


                        horaInicialAula = Convert.ToDateTime(row[3].ToString()).ToString();
                        horaFinalAula = Convert.ToDateTime(row[4].ToString()).ToString();

                        //string hI = Convert.ToDateTime(row[3].ToString()).ToString("hh:mm");
                        //string hF = Convert.ToDateTime(row[4].ToString()).ToString("hh:mm");

                        //inicio de horas AM/PM




                        //int ano = 2008;
                        //int mes = 4;
                        //int dia = 10;
                        //int horaInicial = 16;                        
                        //int minutos = 30;
                        //int segundos = 0;

                        //DateTime date1 = new DateTime(ano, mes, dia, horaInicial, minutos, segundos);   Inserir dentro do método (horaInicialAula.Substring(0,2)                       
                        //string hroaBrasil = date1.ToString("t", CultureInfo.CreateSpecificCulture("pt-BR"));


                        descricaoDisciplina = row[7].ToString();

                        //resgatar codigos do professor


                        string sqlProfessor = "select * from professor where nomeProfessor = '" + nomeProfessor + "'";

                        SqlCommand cmdProfessor = new SqlCommand(sqlProfessor, sqlc);
                        SqlDataReader myReaderProfessor = cmdProfessor.ExecuteReader();



                        if (myReaderProfessor.HasRows)
                        {

                            myReaderProfessor.Read();
                            codigoProfessor = myReaderProfessor["codigoProfessor"].ToString();
                        }
                        myReaderProfessor.Close();                        


                        //resgatar codigos do aluno


                        string sqlAluno = "select * from aluno where nomeAluno = '" + nomeAluno + "' order by nomeAluno";

                        SqlCommand cmdAluno = new SqlCommand(sqlAluno, sqlc);
                        SqlDataReader myReaderAluno = cmdAluno.ExecuteReader();


                        if (myReaderAluno.HasRows)
                        {
                            myReaderAluno.Read();
                            codigoAluno = myReaderAluno["codigoAluno"].ToString();
                        }

                        myReaderAluno.Close();


                        //resgatar codigo da disciplina

                        string sqlDisciplina = "select * from disciplina where descricaoDisciplina = '" + descricaoDisciplina + "'";

                        SqlCommand cmdDisciplina = new SqlCommand(sqlDisciplina, sqlc);
                        SqlDataReader myReaderDisciplina = cmdDisciplina.ExecuteReader();


                        if (myReaderDisciplina.HasRows)
                        {
                            myReaderDisciplina.Read();
                            codigoDisciplina = myReaderDisciplina["codigoDisciplina"].ToString();
                        }
                        myReaderDisciplina.Close();


                        //somente realizara a inserção se a aula nao estiver cadastrada na tabela
                        //fazer select e pertar hasrows


                        // Verificar

                        if ((dataAula != "") && (horaInicialAula != "") && (horaFinalAula != ""))
                        {

                            //dataAula = dataAula.Substring(0, 10);
                            //horaInicialAula = horaInicialAula.Substring(0, 5);
                            //horaFinalAula = horaFinalAula.Substring(0, 5);


                        }


                        if (codigoAluno != "")
                        {
                            if (codigoDisciplina != "")
                            {
                                if (codigoProfessor != "")
                                {
                                    string sqlcadastraAula = " SELECT al.nomeAluno, al.codigoAluno, a.codigoProfessor, a.codigoAluno, a.dataAula, a.horaInicialAula, a.horaFinalAula, a.codigoDisciplina, p.nomeProfessor, p.codigoProfessor";
                                    sqlcadastraAula += " FROM aula a, professor p, aluno al";
                                    sqlcadastraAula += " WHERE p.codigoProfessor = " + codigoProfessor + " AND al.codigoAluno = " + codigoAluno + " AND a.dataAula = '" + dataAula + "' AND a.horaInicialAula = '" + horaInicialAula + "' AND a.horaFinalAula = '" + horaFinalAula + "' AND a.codigoDisciplina = " + codigoDisciplina;


                                    SqlCommand cmdcadastraAula = new SqlCommand(sqlcadastraAula, sqlc);
                                    SqlDataReader myReadercadastraAula = cmdcadastraAula.ExecuteReader();


                                    // if para verificação de registros na tabela 
                                    if (myReadercadastraAula.HasRows)
                                    {
                                        int a = 0;

                                    }
                                    else
                                    {                                        

                                        TimeSpan dif = Convert.ToDateTime(horaFinalAula).Subtract(Convert.ToDateTime(horaInicialAula)); // inserindo horario de 1 em 1 hora

                                        string SqlInserirAula = "";                                        

                                        DateTime hI = Convert.ToDateTime(horaInicialAula);

                                        for (int i = 0; i < dif.TotalHours; i++)
                                        {                                           

                                            DateTime hF = hI;
                                            hF += new TimeSpan(0, 0, 3600);


                                            //Response.Write("dsvfb fdb dfb redbb            " + hI);
                                            //Response.End();

                                            string hora = hI.Hour.ToString();
                                            string minutos = hI.Minute.ToString();

                                            string horaFinal = hF.Hour.ToString();
                                            string minutosFinal = hF.Minute.ToString();

                                            //Response.Write(hora + "<br>");
                                            //Response.Write(minutos + "<br>");
                                            //Response.Write(horaFinal + "<br>");
                                            //Response.Write(minutosFinal + "<br>");
                                            //Response.End();

                                            DateTime InicialInsert = new DateTime(Convert.ToInt16(dataAula.Substring(6, 4)), Convert.ToInt16(dataAula.Substring(3, 2)), Convert.ToInt16(dataAula.Substring(0, 2)), Convert.ToInt16(hora), Convert.ToInt16(minutos), 0);
                                            string horaInicialBrasil = InicialInsert.ToString("t", CultureInfo.CreateSpecificCulture("pt-BR"));


                                            DateTime FinalInsert = new DateTime(Convert.ToInt16(dataAula.Substring(6, 4)), Convert.ToInt16(dataAula.Substring(3, 2)), Convert.ToInt16(dataAula.Substring(0, 2)), Convert.ToInt16(horaFinal), Convert.ToInt16(minutosFinal), 0);
                                            string horaFinalBrasil = FinalInsert.ToString("t", CultureInfo.CreateSpecificCulture("pt-BR"));


                                            //Convert.ToDateTime(myReadercadastraAula["dataInicial"].ToString());
                                                                                     


                                            //Convert.ToDateTime(drAula["dataInicial"].ToString());

                                            //DateTime horaInicialAula = Convert.ToDateTime(horaInicial[i].ToString());
                                            //DateTime horaFinalAula = Convert.ToDateTime(horaFinal[i].ToString());

                                            //Response.Write("aqui");

                                            //Response.End();
                                            

                                            SqlInserirAula = " INSERT into aula ";
                                            SqlInserirAula += "(codigoDisciplina, codigoProfessor, horaInicialAula, horaFinalAula, dataAula, codigoAluno)";
                                            SqlInserirAula += " VALUES ( " + codigoDisciplina + ", " + codigoProfessor + ", '" + horaInicialBrasil + "', '" + horaFinalBrasil + "', '" + dataAula + "' , ";
                                            SqlInserirAula += codigoAluno + ")";

                                            hI = hF;

                                            SqlCommand inserirAula = new SqlCommand(SqlInserirAula, sqld);
                                            inserirAula.ExecuteNonQuery();                                           


                                        }

                                    }                                   


                                    myReadercadastraAula.Close();
                                    

                                    string literalc = "<script language=JavaScript>finalizar();</script>";
                                    form1.Controls.Add(new LiteralControl(literalc));
                                }
                            }

                        }

                    }

                    catch (System.FormatException)
                    {
                        ErroLabel.ForeColor = Color.Red;
                        ErroLabel.Text = "Arquivo gravado";
                        ErroLabel.Visible = true;
                        arquivoGravado = false;
                    }


                }

                sqlc.Close();
                sqld.Close();

            }
        }
    }
}